import sqlite3

from PyQt5.QtSql import QSqlDatabase, QSqlQuery


class MysqlLite:
    def __init__(self):
        '''
        连接数据库
        '''
        try:
            self.database = QSqlDatabase.addDatabase('QSQLITE')
            self.database.setDatabaseName('user.db')  # 设置数据库名字
            if not self.database.open():  # 连接数据库
                print('无法连接数据库:', self.database.lastError().text())
        except Exception as e:
            print('数据库连接错误:', e)



    def execute(self, sql):
        """
        执行SQL语句，根据语句类型返回不同结果
        - 查询语句(SELECT)：返回查询结果列表，每个元素是一行数据的字典
        - 其他语句：返回布尔值表示执行是否成功
        """
        # 创建查询对象
        query = QSqlQuery()

        # 执行SQL语句
        success = query.exec_(sql)
        if not success:
            # 打印错误信息
            print(f"SQL执行失败：{sql}")
            print(f"错误原因：{query.lastError().text()}")
            return False

        return self._handle_query_result(query)

    def _handle_query_result(self, query):
        """处理查询结果，返回列表形式的数据"""
        result = []
        # 获取记录对象，用于获取字段信息
        record = query.record()
        # 获取字段名列表
        columns = [record.fieldName(i) for i in range(record.count())]

        # 遍历查询结果
        while query.next():
            row = {}
            for i, column in enumerate(columns):
                row[column] = query.value(i)
            result.append(row)

        if result == []:
            return True

        return result

    def operation_sql(self, sql, params=None):
        try:
            self.query = QSqlQuery()
            self.query.prepare(sql)  # 准备SQL语句
            if params:
                for i, param in enumerate(params):
                    self.query.bindValue(i, param)
            if not self.query.exec_():
                print('SQL执行错误:',sql)
            return True
        except Exception as e:
            print('执行SQL异常:', e,sql)
            return False

    def select_sql(self, sql, params=None):
        result=[]
        if self.operation_sql(sql, params):
            self.result = self.query.record()
            while self.query.next():
                tem = []
                for i in range(self.result.count()):
                    x=self.query.value(i)
                    tem.append(x)
                result.append(tem)
            return result

    def exit_(self, sql, params=None):
        self.operation_sql(sql, params)
        return self.query.next()

# 获取数据库信息
def get_db_info():
    # 获取所有表名
    db = get_db()
    tables = db.execute("SELECT name AS table_name FROM sqlite_master WHERE type = 'table' ORDER BY name;")
    # print( tables)
    for table in tables:
        table_name = table['table_name']
        # 获取表中的字段
        results = db.execute(f"PRAGMA         table_info({table_name});")
        # print(results)
        table['columns'] = [result['name'] for result in results]

    # print(tables)
    return tables


# 获取数据库连接
def get_db():
    # 创建数据库连接
    my_sqlite = MysqlLite()
    # 启用外键约束
    my_sqlite.execute("PRAGMA  foreign_keys = ON;")
    return my_sqlite